#!/usr/bin/env node

const log=console.log,
    {con}=require('./common.js');

async function getEssayByName(name){
    const sql=`select id,title,publish from essay where title like '%${name}%';`;
    let [rows]=await con.execute(sql);
    return rows
}

async function getEssayByUser(userid){
    const sql=`select essay.id,essay.title,essayimage.img from essay,essayimage where user='${userid}' and essay.examine=1 and essay.id=essayimage.id group by essay.id;`;
    let [rows]=await con.execute(sql);
    return rows
}

async function getEssayUpload(id,title,content,date){
    const sqlCount='select count(id) count from essay';
    let [rowsCount]=await con.execute(sqlCount);
    let count=rowsCount[0].count
    const sql= `insert into essay (id,user,title,content,publish,essay.like,comment,examine) values(${count},'${id}','${title}','${content}','${date}','0','0','0');`
    let [rows]=await con.execute(sql);

    return count;
}

async function getEssayPics(id,path){
        let sqlimg=`insert into essayimage (id,img) values('${id}','${path}');`;
        let [rowimg]=await con.execute(sqlimg)
        log(rowimg)
}

async function delEssay(id){
    let sql=`delete from essay where id='${id}';`;
    let [row]=await con.execute(sql)
    let sqldel=`delete from essayimage where id=${id};`
    let [row1]=await con.execute(sqldel)
    console.log('look here',row1)
    console.log(sqldel)
    return row
}

async function getState(id){
    let sql=`select essay.id,essay.publish,essay.title,essayimage.img from essay,essayimage where user='${id}' and essay.id=essayimage.id and essay.examine=0 group by essay.id;`
    let [check]=await con.execute(sql)
    sql=`select essay.id,essay.publish,essay.title,essayimage.img from essay,essayimage where user='${id}' and essay.id=essayimage.id and essay.examine=1 group by essay.id;`
    let [checkok]=await con.execute(sql)
    sql=`select essay.id,essay.publish,essay.title,essayimage.img from essay,essayimage where user='${id}' and essay.id=essayimage.id and essay.examine=-1 group by essay.id;`
    let [checkno]=await con.execute(sql)
    return {'check':check,'checkok':checkok,'checkno':checkno}
}

module.exports={getEssayByName,getEssayByUser,getEssayUpload,getEssayPics,delEssay,getState}